Core Demo Dashboard¶

Objective: Create a Data Science Notebook, using ECommerce Dataset, to include forecasting visualisations of the created model for the demo dashboard shown to prospective clients.

Created By: Stephen Cole

Creation Date: 2023-04-06

Last Updated¶

%load_ext pretty_jupyter

from datetime import datetime, timedelta

print(f"Last Updated: {datetime.now().strftime('%d/%m/%Y %H:%M:%S')}")

Last Updated: 12/04/2023 16:52:35

Introduction¶

Brazilian E-Commerce Public Dataset by Olist Welcome. This is a Brazilian ecommerce public dataset of orders made at Olist Store. The dataset has information of 100k orders from 2016 to 2018 made at multiple marketplaces in Brazil. It's features allows viewing an order from multiple dimensions: from order status, price, payment and freight performance to customer location, product attributes and finally reviews written by customers. They also released a geolocation dataset that relates Brazilian zip codes to lat/lng coordinates.

A separate dataset was created to supplement the State codes and provide full description of the state, rather than just the code.

1.0 Preliminary Setup¶

.tabset|O_O|.tabset-pills

1.1 Import Packages¶

import os
import time
import logging
import pickle
import yaml
import pandas as pd
import numpy as np

import warnings
warnings.filterwarnings('ignore')

from IPython.display import display, Markdown

from functions.DS_PreProcessing_Functions import reduce_mem_usage, find_column_type

pd.set_option('display.max_columns', None, 'display.max_rows', 500)

1.2 Load Config File¶

path_to_config = "C:/Users/Stephen.Cole/Dropbox/My PC (XT-LPT-012)/Documents/Core Consultants/Internal Dashboard/Python\
/python_config/python_config.yaml"

# Load config
with open (path_to_config,'r') as stream:
    demo_dashboard_config = yaml.safe_load(stream)

1.3 Setup Log File¶

path_to_log = demo_dashboard_config['Paths']['path_to_log']

# Setting up log file
os.makedirs(path_to_log, exist_ok=True)

run_date = datetime.today().strftime('%Y-%m-%d')
log_file_name = run_date + "_demo_dashboard.log"
path_to_log_file = os.path.join(path_to_log, log_file_name)

logging.basicConfig(filename=path_to_log_file,
                   filemode='w',
                   format='%(asctime)s %(name)s %(levelname)s %(message)s',
                   level=logging.INFO,
                   datefmt='%Y-%m-%d')

LOGGER = logging.getLogger(__name__)

1.4 Load Data¶

def create_dfs(dataframe_names, file_paths, log = LOGGER):
    dfs = {}
    for name, file in zip(dataframe_names, file_paths):
        try:
            dfs[name] = pd.read_csv(file)
            LOGGER.info(f"Data file {file} read SUCCESSFULLY")
            LOGGER.info(f"Data file {file} has {dfs[name].shape} [rows, columns]")
        except Exception as e:
            LOGGER.info(f"Data file {file} read UNSUCCESSFULLY with exception {e}")
    return dfs

var_names = ["Customers", "Geolocation", "Items", "Payments", "Reviews", "Orders", 
             "Products", "Sellers", "Products_translation"]
input_file_paths = [os.path.join(demo_dashboard_config['Paths']['path_to_data'], file) for file in demo_dashboard_config['Files']['input_csv']]

dfs = create_dfs(var_names, input_file_paths)

2.0 Data Model¶

There are 100k orders from 2016 to 2018, with 9 datasets to investigate. The Data Model can be described in the image below:

2.1 Principal Dataset¶

Order Reviews Dataset

• Review ID (object)
• Order ID (object)
• Review Score (int64)
• Review Comment Title (object)
• Review Comment Message (object)
• Review Creation Date (object)

• Review Answer Timestamp (object)

  NOTE: review_comment_title and review_comment_message will be converted to English and passed through an NLP model to create a new features that will hopefully help contribute to predicting the review_score

display(dfs["Reviews"].loc[~dfs["Reviews"]["review_comment_title"].isna(),:].set_index("review_id").head())

2.2 Other Datasets¶

.tabset|O_O|.tabset-pills

2.2.1 Customers Dataset¶

• Customer ID (object)
• Customer Unique ID (object)
• Customer ZIP Code Prefix (int64)
• Customer City (object)
• Customer State (object)

display(dfs["Customers"].set_index("customer_id").head())

2.2.2 Geolocation Lookup¶

• Geolocation ZIP Code Prefix (int64)
• Geolocation Latitude (float64)
• Geolocation Longitude (float64)
• Geolocation City (object)
• Geolocation State (object)

NOTE: This Lookup table will not be used for modelling so can be ignore throughout.

display(dfs["Geolocation"].set_index("geolocation_zip_code_prefix").head())

2.2.3 Orders Dataset¶

• Order ID (object)
• Customer ID (object)
• Order Status (object)
• Order Purchase Timestamp (object)
• Order Approved at (object)
• Order Delivered Carrier date (object)
• Order Estimated Delivery date (object)

display(dfs["Orders"].set_index("order_id").head())

2.2.4 Sellers Lookup¶

• Seller ID (object)
• Seller ZIP Code Prefix (int64)
• Seller City (object)
• Seller State (object)

display(dfs["Sellers"].set_index("seller_id").head())

2.2.5 Product Category Name Translation¶

• Product Category Name (object)
• Product Category Name English (object)

display(dfs["Products_translation"].set_index("product_category_name").head())

2.2.6 Product Lookup¶

• Product ID (object)
• Product Category Name (object)
• Product Name Length (float64)
• Product Description Length (float64)
• Product Photos qty (float64)
• Product Weight g (float64)
• Product Length cm (float64)
• Product Height cm (float64)
• Product Width cm (float64)

display(dfs["Products"].set_index("product_id").head())

2.2.7 Order Items Dataset¶

• Order ID (object)
• Order Item ID (int64)
• Product ID (object)
• Seller ID (object)
• Shipping Limit Date (object)
• Price (float64)
• Freight Value (float64)

display(dfs["Items"].set_index("order_id").head())

2.2.8 Order Payments Dataset¶

• Order ID (object)
• Payment Sequential (int64)
• Payment Type (object)
• Payment Installments (int64)
• Payment Value (float64)

display(dfs["Payments"].set_index("order_id").head())

3.0 Creating the "Single Source of Truth" Table¶

Will join all the relevant tables together to create a single table that contains all the useful features. This will make data cleaning, standardisation, and engineering easier as it will only need to go through one cleaning process, rather than 8 separate cleans.

Not all the columns/datasets will be used, so will drop accordingly.

Some useful features can also be created from existing features. For example, waiting duration could affect the rating_score.

df_names = list(dfs.keys())
df_names.remove("Reviews")
df_names.remove("Orders") 
df_names.remove("Geolocation")

modelling_df = pd.merge(dfs["Reviews"], dfs["Orders"], on="order_id", how="left", suffixes=('', '_y'))

for df in df_names:
    print(f"{df} DataFrame has shape {dfs[df].shape}")
    if "order_id" in dfs[df].columns:
        modelling_df = modelling_df.merge(dfs[df], on="order_id", how="left", suffixes=('', '_y'))
    elif "customer_id" in dfs[df].columns:
        modelling_df = modelling_df.merge(dfs[df], on="customer_id", how="left", suffixes=('', '_y'))
    elif "product_id" in dfs[df].columns:
        modelling_df = modelling_df.merge(dfs[df], on="product_id", how="left", suffixes=('', '_y'))
    elif "seller_id" in dfs[df].columns:
        modelling_df = modelling_df.merge(dfs[df], on="seller_id", how="left", suffixes=('', '_y'))
    else:
        modelling_df = modelling_df.merge(dfs[df], on="product_category_name", how="left", suffixes=('', '_y'))
    print(f"{df} has been merged to truth table \n")

cols_reordered = ['review_id', 'order_id','customer_id', 'product_id', 'seller_id', 'product_category_name', 
       'review_comment_title','review_comment_message', 'review_creation_date',
       'review_answer_timestamp', 'order_status',
       'order_purchase_timestamp', 'order_approved_at',
       'order_delivered_carrier_date', 'order_delivered_customer_date',
       'order_estimated_delivery_date', 'customer_unique_id',
       'customer_zip_code_prefix', 'customer_city', 'customer_state',
       'order_item_id',   'shipping_limit_date',
       'price', 'freight_value', 'payment_sequential', 'payment_type',
       'payment_installments', 'payment_value', 
       'product_name_lenght', 'product_description_lenght',
       'product_photos_qty', 'product_weight_g', 'product_length_cm',
       'product_height_cm', 'product_width_cm', 'seller_zip_code_prefix',
       'seller_city', 'seller_state', 'product_category_name_english', 'review_score']

modelling_df.drop_duplicates(subset=["order_id","customer_id","product_id","seller_id","product_category_name"], 
                             inplace=True, keep="last")
display(modelling_df[cols_reordered].head())

Customers has shape (99441, 5)
Customers has been merged to truth table 

Items has shape (112650, 7)
Items has been merged to truth table 

Payments has shape (103886, 5)
Payments has been merged to truth table 

Products has shape (32951, 9)
Products has been merged to truth table 

Sellers has shape (3095, 4)
Sellers has been merged to truth table 

Products_translation has shape (71, 2)
Products_translation has been merged to truth table 

modelling_df.info(verbose=True)

# Memory optimisation of DataFrame - see DS_PreProcessing_Functions.py file for detail
modelling_df = reduce_mem_usage(modelling_df)

Memory usage of dataframe is 32.03 MB
Memory usage after optimization is: 43.56 MB
Decreased by -36.0%

modelling_df.info(verbose=True)

<class 'pandas.core.frame.DataFrame'>
Int64Index: 102384 entries, 1 to 118145
Data columns (total 40 columns):
 #   Column                         Non-Null Count   Dtype   
---  ------                         --------------   -----   
 0   review_id                      102384 non-null  category
 1   order_id                       102384 non-null  category
 2   review_score                   102384 non-null  int8    
 3   review_comment_title           12124 non-null   category
 4   review_comment_message         42870 non-null   category
 5   review_creation_date           102384 non-null  category
 6   review_answer_timestamp        102384 non-null  category
 7   customer_id                    102384 non-null  category
 8   order_status                   102384 non-null  category
 9   order_purchase_timestamp       102384 non-null  category
 10  order_approved_at              102229 non-null  category
 11  order_delivered_carrier_date   100619 non-null  category
 12  order_delivered_customer_date  99502 non-null   category
 13  order_estimated_delivery_date  102384 non-null  category
 14  customer_unique_id             102384 non-null  category
 15  customer_zip_code_prefix       102384 non-null  int32   
 16  customer_city                  102384 non-null  category
 17  customer_state                 102384 non-null  category
 18  order_item_id                  101628 non-null  float32 
 19  product_id                     101628 non-null  category
 20  seller_id                      101628 non-null  category
 21  shipping_limit_date            101628 non-null  category
 22  price                          101628 non-null  float32 
 23  freight_value                  101628 non-null  float32 
 24  payment_sequential             102383 non-null  float32 
 25  payment_type                   102383 non-null  category
 26  payment_installments           102383 non-null  float32 
 27  payment_value                  102383 non-null  float32 
 28  product_category_name          100180 non-null  category
 29  product_name_lenght            100180 non-null  float32 
 30  product_description_lenght     100180 non-null  float32 
 31  product_photos_qty             100180 non-null  float32 
 32  product_weight_g               101612 non-null  float32 
 33  product_length_cm              101612 non-null  float32 
 34  product_height_cm              101612 non-null  float32 
 35  product_width_cm               101612 non-null  float32 
 36  seller_zip_code_prefix         101628 non-null  float32 
 37  seller_city                    101628 non-null  category
 38  seller_state                   101628 non-null  category
 39  product_category_name_english  100158 non-null  category
dtypes: category(24), float32(14), int32(1), int8(1)
memory usage: 43.6 MB

# Check each column for NULLS
null_dict = {}
for df_name, df in dfs.items():
    null_dict[df_name] = [col for col in df.columns if any(df[col].isna())]

print(null_dict)

null_dict

NOTE: review_comment_title and review_comment_message wont be used in the model, so can be dropped instantly